using System.Data;
using SPI.AgentServer.Models;
using Configuration = SPI.AgentServer.Configuration;

namespace SPI.AgentServer.Services.Execution;

/// <summary>
/// 模拟数据库执行器 - 返回示例数据，无需真实数据库连接
/// </summary>
public class MockDatabaseExecutor : IDatabaseExecutor
{
    private readonly ILogger<MockDatabaseExecutor> _logger;
    private readonly Random _random = new();

    public MockDatabaseExecutor(ILogger<MockDatabaseExecutor> logger)
    {
        _logger = logger;
    }

    public async Task<QueryExecutionResult> ExecuteQueryAsync(string sql, string? databaseName = null, CancellationToken cancellationToken = default)
    {
        _logger.LogInformation("Mock executing query on database {Database}: {Sql}", databaseName ?? "default", sql);

        // 模拟延迟
        await Task.Delay(100, cancellationToken);

        var sqlLower = sql.ToLower();

        // 根据SQL生成模拟数据
        if (sqlLower.Contains("count(*)"))
        {
            var data = new List<Dictionary<string, object?>>
            {
                new() { ["COUNT"] = _random.Next(10, 100) }
            };

            return new QueryExecutionResult
            {
                Success = true,
                Data = data,
                RowCount = 1,
                ExecutionTimeMs = _random.Next(10, 50)
            };
        }
        else if (sqlLower.Contains("from students"))
        {
            var data = new List<Dictionary<string, object?>>
            {
                new() { ["id"] = 1, ["name"] = "张三", ["age"] = 18, ["class"] = "高三1班", ["grade"] = "A" },
                new() { ["id"] = 2, ["name"] = "李四", ["age"] = 17, ["class"] = "高三2班", ["grade"] = "B" },
                new() { ["id"] = 3, ["name"] = "王五", ["age"] = 18, ["class"] = "高三1班", ["grade"] = "A" },
                new() { ["id"] = 4, ["name"] = "赵六", ["age"] = 17, ["class"] = "高三3班", ["grade"] = "B" },
                new() { ["id"] = 5, ["name"] = "钱七", ["age"] = 18, ["class"] = "高三2班", ["grade"] = "A" }
            };

            return new QueryExecutionResult
            {
                Success = true,
                Data = data,
                RowCount = 5,
                ExecutionTimeMs = _random.Next(10, 50)
            };
        }
        else if (sqlLower.Contains("from courses"))
        {
            var data = new List<Dictionary<string, object?>>
            {
                new() { ["id"] = 1, ["name"] = "数学", ["teacher"] = "张老师", ["credits"] = 4, ["department"] = "理科部" },
                new() { ["id"] = 2, ["name"] = "英语", ["teacher"] = "李老师", ["credits"] = 3, ["department"] = "文科部" },
                new() { ["id"] = 3, ["name"] = "物理", ["teacher"] = "王老师", ["credits"] = 3, ["department"] = "理科部" },
                new() { ["id"] = 4, ["name"] = "历史", ["teacher"] = "赵老师", ["credits"] = 2, ["department"] = "文科部" }
            };

            return new QueryExecutionResult
            {
                Success = true,
                Data = data,
                RowCount = 4,
                ExecutionTimeMs = _random.Next(10, 50)
            };
        }
        else if (sqlLower.Contains("from grades"))
        {
            var data = new List<Dictionary<string, object?>>
            {
                new() { ["student_id"] = 1, ["course_id"] = 1, ["score"] = 95, ["semester"] = "春季", ["year"] = 2024 },
                new() { ["student_id"] = 1, ["course_id"] = 2, ["score"] = 88, ["semester"] = "春季", ["year"] = 2024 },
                new() { ["student_id"] = 2, ["course_id"] = 1, ["score"] = 76, ["semester"] = "春季", ["year"] = 2024 },
                new() { ["student_id"] = 2, ["course_id"] = 3, ["score"] = 82, ["semester"] = "春季", ["year"] = 2024 }
            };

            return new QueryExecutionResult
            {
                Success = true,
                Data = data,
                RowCount = 4,
                ExecutionTimeMs = _random.Next(10, 50)
            };
        }
        // 设备相关表
        else if (sqlLower.Contains("tbldevice") || sqlLower.Contains("device"))
        {
            return GenerateDeviceMockData(sqlLower);
        }
        // 报警相关表
        else if (sqlLower.Contains("tblalarm") || sqlLower.Contains("alarm"))
        {
            return GenerateAlarmMockData(sqlLower);
        }
        // 闸机记录表（支持分表）
        else if (sqlLower.Contains("tblgateinformation"))
        {
            return GenerateGateRecordMockData(sqlLower);
        }
        // 人员相关表
        else if (sqlLower.Contains("tblpersonnel") || sqlLower.Contains("personnel"))
        {
            return GeneratePersonnelMockData(sqlLower);
        }
        // 员工表（支持分表）
        else if (sqlLower.Contains("tblemployee"))
        {
            return GenerateEmployeeMockData(sqlLower);
        }
        // 黑名单表
        else if (sqlLower.Contains("tblblacklist") || sqlLower.Contains("blacklist"))
        {
            return GenerateBlacklistMockData(sqlLower);
        }
        // 车辆相关表
        else if (sqlLower.Contains("tblvehicle") || sqlLower.Contains("vehicle"))
        {
            return GenerateVehicleMockData(sqlLower);
        }
        // 访客车辆表
        else if (sqlLower.Contains("tblvisitor"))
        {
            return GenerateVisitorVehicleMockData(sqlLower);
        }
        else
        {
            // 默认返回通用数据
            var data = new List<Dictionary<string, object?>>
            {
                new() { ["id"] = 1, ["value"] = "示例数据1", ["description"] = "这是模拟数据" },
                new() { ["id"] = 2, ["value"] = "示例数据2", ["description"] = "用于测试" },
                new() { ["id"] = 3, ["value"] = "示例数据3", ["description"] = "无需数据库" }
            };

            return new QueryExecutionResult
            {
                Success = true,
                Data = data,
                RowCount = 3,
                ExecutionTimeMs = _random.Next(10, 50)
            };
        }
    }

    private QueryExecutionResult GenerateDeviceMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["DeviceID"] = "CAM-001", ["DeviceName"] = "东门摄像头", ["DeviceType"] = "摄像头", ["Status"] = "在线", ["Location"] = "东门", ["LastOnline"] = DateTime.Now },
            new() { ["DeviceID"] = "CAM-002", ["DeviceName"] = "西门摄像头", ["DeviceType"] = "摄像头", ["Status"] = "离线", ["Location"] = "西门", ["LastOnline"] = DateTime.Now.AddHours(-2) },
            new() { ["DeviceID"] = "GATE-001", ["DeviceName"] = "北门闸机", ["DeviceType"] = "闸机", ["Status"] = "在线", ["Location"] = "北门", ["LastOnline"] = DateTime.Now },
            new() { ["DeviceID"] = "CAM-003", ["DeviceName"] = "图书馆摄像头", ["DeviceType"] = "摄像头", ["Status"] = "故障", ["Location"] = "图书馆", ["LastOnline"] = DateTime.Now.AddDays(-1) },
            new() { ["DeviceID"] = "GATE-002", ["DeviceName"] = "南门闸机", ["DeviceType"] = "闸机", ["Status"] = "在线", ["Location"] = "南门", ["LastOnline"] = DateTime.Now }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateAlarmMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["AlarmID"] = 1, ["DeviceID"] = "CAM-002", ["AlarmType"] = "设备离线", ["AlarmTime"] = DateTime.Now.AddHours(-2), ["Status"] = "未处理", ["Level"] = "高" },
            new() { ["AlarmID"] = 2, ["DeviceID"] = "CAM-003", ["AlarmType"] = "设备故障", ["AlarmTime"] = DateTime.Now.AddDays(-1), ["Status"] = "处理中", ["Level"] = "中" },
            new() { ["AlarmID"] = 3, ["DeviceID"] = "GATE-001", ["AlarmType"] = "异常通行", ["AlarmTime"] = DateTime.Now.AddMinutes(-30), ["Status"] = "已处理", ["Level"] = "低" },
            new() { ["AlarmID"] = 4, ["DeviceID"] = "CAM-001", ["AlarmType"] = "画面异常", ["AlarmTime"] = DateTime.Now.AddHours(-5), ["Status"] = "未处理", ["Level"] = "中" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateGateRecordMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["RecordID"] = 1, ["PersonName"] = "张三", ["PersonID"] = "P001", ["GateID"] = "GATE-001", ["Direction"] = "进", ["Time"] = DateTime.Now.AddHours(-2), ["Department"] = "计算机系" },
            new() { ["RecordID"] = 2, ["PersonName"] = "李四", ["PersonID"] = "P002", ["GateID"] = "GATE-002", ["Direction"] = "进", ["Time"] = DateTime.Now.AddHours(-3), ["Department"] = "数学系" },
            new() { ["RecordID"] = 3, ["PersonName"] = "王五", ["PersonID"] = "P003", ["GateID"] = "GATE-001", ["Direction"] = "出", ["Time"] = DateTime.Now.AddHours(-1), ["Department"] = "物理系" },
            new() { ["RecordID"] = 4, ["PersonName"] = "赵六", ["PersonID"] = "P004", ["GateID"] = "GATE-003", ["Direction"] = "进", ["Time"] = DateTime.Now.AddMinutes(-30), ["Department"] = "化学系" },
            new() { ["RecordID"] = 5, ["PersonName"] = "钱七", ["PersonID"] = "P005", ["GateID"] = "GATE-002", ["Direction"] = "出", ["Time"] = DateTime.Now.AddMinutes(-15), ["Department"] = "计算机系" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GeneratePersonnelMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["PersonID"] = "P001", ["Name"] = "张教授", ["Department"] = "计算机系", ["Type"] = "教师", ["Status"] = "在校", ["CardNo"] = "T001", ["Phone"] = "13800001111" },
            new() { ["PersonID"] = "P002", ["Name"] = "李博士", ["Department"] = "数学系", ["Type"] = "教师", ["Status"] = "在校", ["CardNo"] = "T002", ["Phone"] = "13800002222" },
            new() { ["PersonID"] = "S001", ["Name"] = "王同学", ["Department"] = "计算机系", ["Type"] = "学生", ["Status"] = "在校", ["CardNo"] = "S001", ["Phone"] = "13900001111" },
            new() { ["PersonID"] = "S002", ["Name"] = "赵同学", ["Department"] = "物理系", ["Type"] = "学生", ["Status"] = "离校", ["CardNo"] = "S002", ["Phone"] = "13900002222" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateEmployeeMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["EmployeeID"] = "E001", ["Name"] = "王主任", ["Department"] = "行政部", ["Position"] = "主任", ["EntryDate"] = "2020-03-15", ["Status"] = "在职" },
            new() { ["EmployeeID"] = "E002", ["Name"] = "李经理", ["Department"] = "后勤部", ["Position"] = "经理", ["EntryDate"] = "2019-06-01", ["Status"] = "在职" },
            new() { ["EmployeeID"] = "E003", ["Name"] = "赵主管", ["Department"] = "安保部", ["Position"] = "主管", ["EntryDate"] = "2021-01-10", ["Status"] = "在职" },
            new() { ["EmployeeID"] = "E004", ["Name"] = "刘助理", ["Department"] = "行政部", ["Position"] = "助理", ["EntryDate"] = "2022-09-01", ["Status"] = "在职" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateBlacklistMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["PersonID"] = "BL001", ["Name"] = "可疑人员A", ["Reason"] = "多次违规", ["AddedDate"] = DateTime.Now.AddMonths(-2), ["Status"] = "活跃", ["LastSeen"] = DateTime.Now.AddDays(-1) },
            new() { ["PersonID"] = "BL002", ["Name"] = "可疑人员B", ["Reason"] = "安全隐患", ["AddedDate"] = DateTime.Now.AddMonths(-3), ["Status"] = "监控中", ["LastSeen"] = DateTime.Now.AddDays(-5) }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateVehicleMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["VehicleID"] = "V001", ["PlateNumber"] = "京A12345", ["OwnerName"] = "张三", ["VehicleType"] = "小汽车", ["Status"] = "在校", ["EntryTime"] = DateTime.Now.AddHours(-3), ["Location"] = "停车场A" },
            new() { ["VehicleID"] = "V002", ["PlateNumber"] = "京B67890", ["OwnerName"] = "李四", ["VehicleType"] = "SUV", ["Status"] = "在校", ["EntryTime"] = DateTime.Now.AddHours(-5), ["Location"] = "停车场B" },
            new() { ["VehicleID"] = "V003", ["PlateNumber"] = "沪C11111", ["OwnerName"] = "王五", ["VehicleType"] = "小汽车", ["Status"] = "已离开", ["EntryTime"] = DateTime.Now.AddDays(-1), ["Location"] = null },
            new() { ["VehicleID"] = "V004", ["PlateNumber"] = "粤D22222", ["OwnerName"] = "赵六", ["VehicleType"] = "电动车", ["Status"] = "在校", ["EntryTime"] = DateTime.Now.AddDays(-3), ["Location"] = "停车场C" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    private QueryExecutionResult GenerateVisitorVehicleMockData(string sql)
    {
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["VisitorID"] = "VV001", ["PlateNumber"] = "京E33333", ["VisitorName"] = "外来访客1", ["Purpose"] = "参观交流", ["EntryTime"] = DateTime.Now.AddHours(-2), ["ExpectedExitTime"] = DateTime.Now.AddHours(2) },
            new() { ["VisitorID"] = "VV002", ["PlateNumber"] = "津F44444", ["VisitorName"] = "外来访客2", ["Purpose"] = "学术会议", ["EntryTime"] = DateTime.Now.AddHours(-4), ["ExpectedExitTime"] = DateTime.Now.AddHours(1) },
            new() { ["VisitorID"] = "VV003", ["PlateNumber"] = "冀G55555", ["VisitorName"] = "外来访客3", ["Purpose"] = "业务洽谈", ["EntryTime"] = DateTime.Now.AddMinutes(-30), ["ExpectedExitTime"] = DateTime.Now.AddHours(3) }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = data.Count,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }
    
    private QueryExecutionResult GenerateDefaultMockData()
    {
        // 默认返回通用数据
        var data = new List<Dictionary<string, object?>>
        {
            new() { ["id"] = 1, ["value"] = "示例数据1", ["description"] = "这是模拟数据" },
            new() { ["id"] = 2, ["value"] = "示例数据2", ["description"] = "用于测试" },
            new() { ["id"] = 3, ["value"] = "示例数据3", ["description"] = "无需数据库" }
        };

        return new QueryExecutionResult
        {
            Success = true,
            Data = data,
            RowCount = 3,
            ExecutionTimeMs = _random.Next(10, 50)
        };
    }

    public Task<bool> TestConnectionAsync(string databaseName, CancellationToken cancellationToken = default)
    {
        _logger.LogInformation("Mock testing connection to database: {Database}", databaseName);
        
        // 模拟连接总是成功
        return Task.FromResult(true);
    }
}






